package Actions;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.struts.action.Action;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;

import forms.degreecollection;

public class showInfoAction extends Action {		
		public ActionForward execute(ActionMapping mapping, ActionForm form,
				HttpServletRequest request, HttpServletResponse response){
			
			forms.showInfoForm xx = (forms.showInfoForm) form;
			xx.reset();
			String uname = (String) request.getParameter("username");
			xx.setUsername(uname);
			
			
			Connection conn = null;
			Statement stmt = null;
			Statement stmt1 = null;
			Statement stmt2 = null;
			Statement stmt3 = null;
			ResultSet rs = null;
			ResultSet rs1 = null;
			ResultSet rs2 = null;
			ResultSet rs3 = null;
			
			int app = -1;
			
			try {
				String rev = ""
				// Registering Postgresql JDBC driver
				Class.forName("org.postgresql.Driver");
				// Open a connection to the database	
				conn = DriverManager.getConnection("jdbc:postgresql://localhost/cse135?" +
		                "user=postgres&password=password");
				// Create the statement
			    stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
			                				ResultSet.CONCUR_UPDATABLE);
				
				rs = stmt.executeQuery("SELECT user_id FROM users WHERE user_name = '" + uname + "'");
				while( rs.next() ){
					rev = rs.getString("user_id");
				}
				
			} catch (SQLException e) 
				{ 
					System.out.println(e);
				} catch (ClassNotFoundException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					}
			
			// Registering Postgresql JDBC driver
			try {
				Class.forName("org.postgresql.Driver");
				// Open a connection to the database	
				conn = DriverManager.getConnection("jdbc:postgresql://localhost/cse135?" +
		                "user=postgres&password=password");
				// Create the statement
			    stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
			                				ResultSet.CONCUR_UPDATABLE);
			    stmt1 = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
        				ResultSet.CONCUR_UPDATABLE);
			    stmt2 = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
        				ResultSet.CONCUR_UPDATABLE);
			    stmt3 = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
        				ResultSet.CONCUR_UPDATABLE);
				String first =null;
				String last = null;
				String middle = null;
				int applicant;
				
				rs = stmt.executeQuery("SELECT application FROM reviewer_assignments WHERE reviewer ='"+rev+"'");
				
				while( rs.next() ){
					applicant = rs.getInt("application");
				}
				
			    rs = stmt.executeQuery("SELECT first_name, middle_name, last_name FROM applicants WHERE id ='"+applicant+"'");
			    
				while( rs.next() ){
					first = rs.getString("first_name");
					middle = rs.getString("middle_name");
					last = rs.getString("last_name");
				}

			
				 String street = null;
				 String city = null;
				 String lstate =null;
				 String zip = null;
				 String areaCode =null;
				 String pnumber = null;
				 String specialization = null;
				 String residence = null;
				 String residency = null;
				 String citizen = null;
				 String status = null;
				 boolean us_res = false;

				rs = stmt.executeQuery("SELECT street_address, city, state, zip_code, area_code, phone_number FROM applicants WHERE id = '"
							+applicant+"'");
				 
				while(rs.next()){
					street = rs.getString("street_address");
					city = rs.getString("city");
					lstate = rs.getString("state");
					zip = rs.getString("zip_code");
					areaCode = rs.getString("area_code");
					pnumber = rs.getString("phone_number");
					
				}


				
				rs = stmt.executeQuery("SELECT status, us_resident FROM applicants WHERE id = '"
						+applicant+"'");
				
			 
				while(rs.next()){
					
					status = rs.getString("status");
					us_res = rs.getBoolean("us_resident");
				}
				/*get citizen country */
				
				int citz = 0;
				
				rs = stmt.executeQuery("SELECT citizenship FROM applicants WHERE id = '"
						+applicant+"'");
				
				while(rs.next()){
					citz = rs.getInt("citizenship");
				}
				
				rs = stmt.executeQuery("SELECT country FROM countries WHERE id = '"
						+citz+"'");
				
				while (rs.next()){
					citizen = rs.getString("country");
				}
				/*get residency country */
				int res = 0;
				rs = stmt.executeQuery("SELECT residency FROM applicants WHERE id = '"
						+applicant+"'");
				
				while(rs.next()){
					res = rs.getInt("residency");
				}
				
				rs = stmt.executeQuery("SELECT countries FROM countries WHERE id = '"
						+res+"'");
				
				while (rs.next()){
					residency = rs.getString("country");
				}
				
				/*get specialization */
				int spec = 0;
				
				rs = stmt.executeQuery("SELECT specialization FROM applicants WHERE id = '"
						+applicant+"'");
				
				while(rs.next()){
					spec = rs.getInt("specialization");
				}
				
				rs = stmt.executeQuery("SELECT specialization FROM specializations WHERE id = '"
						+spec+"'");
				
				while (rs.next()){
					specialization = rs.getString("specialization");
				}
				
				/*continue from here */
				rs1 = stmt1.executeQuery("SELECT id FROM degrees WHERE completed_by = '"
						+ applicant + "'");
				while(rs1.next()){
				
						rs = stmt.executeQuery("SELECT * FROM degrees WHERE id= '"
								+rs1.getInt("id") +"'");
						rs2 = stmt2.executeQuery("SELECT university, location FROM universities WHERE id = "
								+"(SELECT university FROM degrees WHERE id=  '"
								+rs1.getInt("id") +"')");

						rs3 = stmt3.executeQuery("SELECT discipline FROM disciplines WHERE id = "
								+"(SELECT discipline FROM degrees WHERE id=  '"
								+rs1.getInt("id") +"')");

					 
						while(rs.next()&&rs2.next()&&rs3.next()){
							forms.degreecollection newDegree = new degreecollection(rs2.getString("location"),rs2.getString("university"),
									rs3.getString("discipline"),rs.getString("grad_month"),rs.getString("grad_year"),rs.getString("gpa"),rs.getString("title"),
									rs.getString("transcript"));
							xx.updateCollection(newDegree);
						}
				}
				
				
				/* get comments/grades */
				rs = stmt.executeQuery("SELECT comment, grade FROM reviewer_assignments WHERE id = '" + applicant + "'");
				while(rs.next()){
					if(rs.getString("comment")!=null){
						xx.updateRvcomments(new forms.reviewComments(rs.getString("comment"),rs.getInt("grade")));
						xx.setRvCount(1);
				}
				
				
				
				
				
				xx.setFirst(first);
				xx.setLast(last);
				xx.setMiddle(middle);
				xx.setAreaCode(areaCode);
				xx.setStreet(street);
				xx.setPnumber(pnumber);
				xx.setLstate(lstate);
				xx.setCity(city);
				xx.setZip(zip);
				xx.setSpecialization(specialization);
				xx.setResidence(residence);
				xx.setResidency(residency);
				xx.setCitizen(citizen);
				xx.setStatus(status);
				
				
				rs.close();
				stmt.close();
				rs2.close();
				stmt2.close();
				rs3.close();
				stmt3.close();
				conn.close();
					
			} catch (ClassNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			
			System.out.println(request.getParameter("username"));
		    	return mapping.findForward("show");
		}
	}
